#
# This file is a part of the open-eBackup project.
# This Source Code Form is subject to the terms of the Mozilla Public License, v. 2.0.
# If a copy of the MPL was not distributed with this file, You can obtain one at
# http://mozilla.org/MPL/2.0/.
#
# Copyright (c) [2024] Huawei Technologies Co.,Ltd.
#
# THIS SOFTWARE IS PROVIDED ON AN "AS IS" BASIS, WITHOUT WARRANTIES OF ANY KIND,
# EITHER EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO NON-INFRINGEMENT,
# MERCHANTABILITY OR FIT FOR A PARTICULAR PURPOSE.
#

import locale
import os

from oracle import logger
from common.common import read_result_file
from common.file_common import delete_file
from oracle.common.constants import ScriptExitCode, PluginPathConstant
from oracle.common.windows_common import write_tmp_file, execute_windows_cmd_with_low_auth_user, get_sql_error_info, \
    get_db_role, set_db_silence_sql, execute_windows_sqlplus_cmd


def execute_windows_sqlplus_cmd_for_exact_identifier(params, tmp_sql, tmp_rst, correct_identifier):
    # 解析params信息
    pid = params.get("pid", "")
    db_user = params.get("db_user", "")
    db_password = params.get("db_password", "")

    if db_password:
        db_password = f'"{db_password}"'

    # 执行命令
    os.environ["ORACLE_SID"] = params.get("instance_name", "")
    os.environ["NLS_LANG"] = "American_America.US7ASCII"
    cmd_list = f'sqlplus /nolog'.split()
    input_cmd = f'conn {db_user}/{db_password}@{correct_identifier}  as sysdba\n' \
                f'@{tmp_sql}\n'

    ret, result = execute_windows_cmd_with_low_auth_user(pid, cmd_list, input_cmd)

    # 输出错误码以及错误详情文件
    if not ret or ("ERROR" in result):
        logger.error("Execute oracle sqlplus cmd failed. pid = %s", pid)
        result_code = ScriptExitCode.ERROR_SCRIPT_EXEC_FAILED
        get_sql_error_info(pid, tmp_rst, "sqlplus", result_code)
        return ScriptExitCode.ERROR_ORACLE_EXESQL_FAILED
    return ScriptExitCode.SUCCESS


def check_standby_database_sequence(params, oracle_home):
    pid = params.get("pid", "")
    instance_name = params.get("instance_name", "")
    db_user = params.get("db_user", "")
    db_password = params.get("db_password", "")
    ret, db_role = get_db_role(pid, db_user, db_password, instance_name)
    if ret != ScriptExitCode.SUCCESS:
        logger.error("Get db_role failed. pid = %s", pid)
        return ret
    if db_role != "PRIMARY":
        # 登录数据库获取db_name和db_unique_name
        db_name, db_unique_name = get_db_name_and_db_unique_name(params)

        # 获取备库最大sequence值
        standby_max_sequence = get_standby_max_sequence(params)

        # 打开tnsnames.ora，获取connect identifier
        identifiers = get_connect_identifier(oracle_home)

        # 遍历登录identifier，找到数据库名一致，unique_name不一致的
        ret, correct_identifier = find_right_identifier(params, identifiers, db_name, db_unique_name)
        if ret != ScriptExitCode.SUCCESS or correct_identifier == "":
            logger.error("find correct identifier FAILED!")
            return ret

        # 登录到这个identifier，获取其最大sequence
        primary_max_sequence = get_primary_max_sequence(params, correct_identifier)
        # 对比主备库的最大sequence
        if primary_max_sequence > standby_max_sequence:
            logger.error("The max sequence of primary is larger than standby!")
            return ScriptExitCode.ERROR_PRIMARY_STANDBY_DIFFERENT

    # 否则返回成功
    logger.info(f"The sequences are consistent!")
    return ScriptExitCode.SUCCESS


def get_db_name_and_db_unique_name(params):
    pid = params.get("pid", "")
    tmp_sql = f"{PluginPathConstant.WINDOWS_TMP_PATH}/get_db_name_sql_{pid}.sql"
    tmp_rst = f"{PluginPathConstant.WINDOWS_TMP_PATH}/get_db_name_sql_{pid}.txt"
    sql_cmd = f"SPOOL {tmp_rst}\n" \
              f"set linesize 999\n" \
              f"select name, db_unique_name from v$database;\n" \
              f"SPOOL OFF;\n" \
              f"exit;\n"
    write_tmp_file(tmp_sql, sql_cmd)
    set_db_silence_sql(tmp_sql)

    ret_code = execute_windows_sqlplus_cmd(pid, params, tmp_sql, tmp_rst, params.get("instance_name", ""))
    if ret_code != ScriptExitCode.SUCCESS:
        logger.error(f"Get database name and unique name failed, ret={ret_code}, error is cat {tmp_rst}")
        delete_file(tmp_sql)
        delete_file(tmp_rst)
        return "", ""
    encoding = locale.getdefaultlocale()[1]
    content = read_result_file(tmp_rst, encoding=encoding).strip()
    logger.info(f"The db_name_and_db_unique_name = {content}.")
    db_name = content.split()[0].strip()
    db_unique_name = content.split()[1].strip()
    delete_file(tmp_sql)
    delete_file(tmp_rst)
    return db_name, db_unique_name


def get_standby_max_sequence(params):
    pid = params.get("pid", "")
    tmp_sql = f"{PluginPathConstant.WINDOWS_TMP_PATH}/get_standby_max_sequence_sql_{pid}.sql"
    tmp_rst = f"{PluginPathConstant.WINDOWS_TMP_PATH}/get_standby_max_sequence_rst_{pid}.txt"
    sql_cmd = f"SPOOL {tmp_rst}\n" \
              f"set linesize 999\n" \
              f"select max(sequence#) from v$archived_log;\n" \
              f"SPOOL OFF;\n" \
              f"exit;\n"
    write_tmp_file(tmp_sql, sql_cmd)
    set_db_silence_sql(tmp_sql)

    ret_code = execute_windows_sqlplus_cmd(pid, params, tmp_sql, tmp_rst, params.get("instance_name", ""))
    if ret_code != ScriptExitCode.SUCCESS:
        logger.error(f"Get database name and unique name failed, ret={ret_code}, error is cat {tmp_rst}")
        delete_file(tmp_sql)
        delete_file(tmp_rst)
        return "0"
    encoding = locale.getdefaultlocale()[1]
    content = read_result_file(tmp_rst, encoding=encoding).strip()
    logger.info(f"The standby_max_sequence = {content}.")
    delete_file(tmp_sql)
    delete_file(tmp_rst)
    return content


def get_connect_identifier(oracle_home):
    if not os.path.exists(f"{oracle_home}/network/admin/tnsnames.ora"):
        logger.error("tnsnames.ora not exist.")
        return []
    encoding = locale.getdefaultlocale()[1]
    out_info = read_result_file(f"{oracle_home}/network/admin/tnsnames.ora", encoding=encoding).splitlines()

    result = []
    for line in out_info:
        line = line.strip()
        if line:
            flag = not line.startswith("#") and not line.startswith("(") and not line.startswith(")")
            if flag:
                result.append(line.split("=")[0].strip())
    logger.info(f"identifier:{result}")
    return result


def find_right_identifier(params, identifiers, target_db_name, target_db_unique_name):
    pid = params.get("pid", "")
    tmp_sql = f"{PluginPathConstant.WINDOWS_TMP_PATH}/get_right_identifier_sql_{pid}.sql"
    tmp_rst = f"{PluginPathConstant.WINDOWS_TMP_PATH}/get_right_identifier_rst_{pid}.txt"
    sql_cmd = f"SPOOL {tmp_rst}\n" \
              f"set linesize 999\n" \
              f"select name, db_unique_name, database_role from v$database;\n" \
              f"SPOOL OFF;\n" \
              f"exit;\n"
    write_tmp_file(tmp_sql, sql_cmd)
    set_db_silence_sql(tmp_sql)
    for correct_identifier in identifiers:
        ret_code = execute_windows_sqlplus_cmd_for_exact_identifier(params, tmp_sql, tmp_rst, correct_identifier)
        if ret_code != ScriptExitCode.SUCCESS:
            logger.error(f"Login to the specific identifier failed.")
            continue
        encoding = locale.getdefaultlocale()[1]
        content = read_result_file(tmp_rst, encoding=encoding).strip()
        curr_db_name = content.split()[0].strip()
        curr_db_unique_name = content.split()[1].strip()
        curr_db_role = content.split()[2].strip()
        if curr_db_role == "PRIMARY" and curr_db_name == target_db_name \
                and curr_db_unique_name != target_db_unique_name:
            delete_file(tmp_sql)
            delete_file(tmp_rst)
            logger.info("Find the correct identifier!")
            return ScriptExitCode.SUCCESS, correct_identifier
    delete_file(tmp_sql)
    delete_file(tmp_rst)
    return ScriptExitCode.SUCCESS, ""


def get_primary_max_sequence(params, correct_identifier):
    pid = params.get("pid", "")
    tmp_sql = f"{PluginPathConstant.WINDOWS_TMP_PATH}/get_primary_max_sequence_sql_{pid}.sql"
    tmp_rst = f"{PluginPathConstant.WINDOWS_TMP_PATH}/get_primary_max_sequence_rst_{pid}.txt"
    sql_cmd = f"SPOOL {tmp_rst}\n" \
              f"set linesize 999\n" \
              f"select max(sequence#) from v$archived_log;\n" \
              f"SPOOL OFF;\n" \
              f"exit;\n"
    write_tmp_file(tmp_sql, sql_cmd)
    set_db_silence_sql(tmp_sql)

    ret_code = execute_windows_sqlplus_cmd_for_exact_identifier(params, tmp_sql, tmp_rst, correct_identifier)
    if ret_code != ScriptExitCode.SUCCESS:
        logger.error(f"Get primary_max_seq failed, ret={ret_code}, error is cat {tmp_rst}")
        delete_file(tmp_sql)
        delete_file(tmp_rst)
        return "0"
    encoding = locale.getdefaultlocale()[1]
    content = read_result_file(tmp_rst, encoding=encoding).strip()
    logger.info(f"The primary_max_seq = {content}.")
    delete_file(tmp_sql)
    delete_file(tmp_rst)
    return content
